package org.fan;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.exception.MYPOIException;


public class ExcelToTextTools {

	/**
	 * @param args
	 * @throws MYPOIException 
	 * @throws IOException 
	 */
	public static void main(String[] args) throws  MYPOIException {
		//1. 获取配置参数
		PropertiesVO propertiesVO = initConfigurations(true);
		String excelFilePath = propertiesVO.getExcelFileName();
		String txtFilePath = propertiesVO.getToTxtFileStartName();
		//读取工作表配置，需要作为输入参数，可以考虑从args中获取
		int sheetflag = propertiesVO.getSheetflag();
		txtFilePath = propertiesVO.getToTxtFileStartName();
		//写数据
		writeFile(getDataFromExcel(excelFilePath,sheetflag),txtFilePath);
		
	}
	/**
	 * 读取excel文件的填入的数据
	 * @param excelFilePath
	 * @param sheetFlag
	 * @return
	 * @throws MYPOIException 
	 */
	private static List<StringBuffer> getDataFromExcel(String excelFilePath,int sheetFlag) throws MYPOIException {
		//从excel里读取数据源头
		List<ExcelDataVO> dataStrList = new ArrayList<ExcelDataVO>();
		ExcelDataVO excelDataVO = new ExcelDataVO();
		HSSFWorkbook workbook = new HSSFWorkbook();
		String clumnName;
		List<Object>  columnValueArray;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		List<StringBuffer> sbResultArray = new ArrayList<StringBuffer>();
		File uploadFile = new File(excelFilePath);
		try {
			FileInputStream fileInputStream = new FileInputStream(uploadFile);
			workbook = new HSSFWorkbook(fileInputStream);
		} catch (Exception e) {
			String exceptonMSG = "读取文件发生异常,文件信息："+uploadFile +"/n原始异常信息："+e.getLocalizedMessage();
			throw new MYPOIException(exceptonMSG,MYPOIException.MYPOIEXCEPIONTYPE_PRINTLN); 
		}
		sheet = workbook.getSheetAt(sheetFlag);
		int rows = sheet.getPhysicalNumberOfRows();
		
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		HSSFDataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("@"));

		
		row =  sheet.getRow(0);
		int clumnNameFlag = ExcelDataVO.getColumnNameIndex(row);
		int isUsedFlag  = ExcelDataVO.getIsUsedFlag(row);
		
		List<Integer> clumnValueArrayFlag = ExcelDataVO.getColumnValueIndexs(row);
		if(clumnNameFlag==-1){
			String exceptonMSG = "未在EXCEL中获取到列头信息，请检查是否存在列名为" +ExcelDataVO.COLUMNNAMETILE + "的列\n";
			throw new MYPOIException(exceptonMSG,MYPOIException.MYPOIEXCEPIONTYPE_PRINTLN);
		}
		if(isUsedFlag==-1){
			String exceptonMSG = "未在EXCEL中获取到控制是否输出信息的列，请检查是否存在列名为" +ExcelDataVO.COLUMNISUSED + "的列\n";
			throw new MYPOIException(exceptonMSG,MYPOIException.MYPOIEXCEPIONTYPE_PRINTLN);
		}
		for(int j=1;j<rows;j++){
			row =  sheet.getRow(j);
			//添加控制，是否需要输出
			if(row.getCell(isUsedFlag)==null||!row.getCell(isUsedFlag).toString().equals("1")){
				continue;
			}
			clumnName = row.getCell(clumnNameFlag)==null?"":row.getCell(clumnNameFlag).toString();
			columnValueArray = new ArrayList<Object>();
			if(clumnValueArrayFlag==null||clumnValueArrayFlag.size()==0){
				excelDataVO = new ExcelDataVO(clumnName,columnValueArray);
			}else{
				for(Integer flag:clumnValueArrayFlag){
					cell = row.getCell(flag);
					if(cell!=null){
						if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
							DecimalFormat df = new DecimalFormat("#");
							columnValueArray.add(df.format(cell.getNumericCellValue()));
						}else{
							columnValueArray.add(cell.toString());
						}
					}else{
						columnValueArray.add("");
					}
				}
				excelDataVO = new ExcelDataVO(clumnName,columnValueArray);
			}
			dataStrList.add(excelDataVO);
		} 
		
		//取到数据后根据列表顺序输出
		sbResultArray = new ArrayList<StringBuffer>(clumnValueArrayFlag.size());
		for(@SuppressWarnings("unused") Integer sbflag:clumnValueArrayFlag){
			sbResultArray.add(new StringBuffer("{"));
		}
			
		for(ExcelDataVO vo :dataStrList){
			int oflag = 0;
			for(Object obj:vo.getColumnValueArray()){
				sbResultArray.get(oflag++).append(vo.getColumnName().trim()+"="+obj+", ");
			}
		}
			
		for(StringBuffer sb:sbResultArray){
			sb.delete(sb.length()-",".length()-1, sb.length()-1);
			sb.append("}");
		}
		return sbResultArray;
	}
    /**
     * 将sbArrays字符串写入filePath文件 txt格式
     * @param sbArrays 数组类型，一个数据写一个文件
     * @param filePath
     * @throws MYPOIException 
     * @throws IOException
     */
	private static void writeFile(List<StringBuffer> sbArrays,String filePath) throws MYPOIException{
		for(StringBuffer sb: sbArrays){
			//sb = new StringBuffer("{" + sb.append("}"));
			File file = new File(filePath +  Math.round(Math.random()*100000));
			FileOutputStream fos = null;
			try {
				fos = new FileOutputStream(file);
				fos.write(sb.toString().trim().getBytes());
				fos.flush();
				System.out.println("成功输出文件："+file.getAbsolutePath());
			} catch (FileNotFoundException e) {
				throw new MYPOIException("未找到文件异常，文件名"+filePath +"/n原始异常信息："+e.getLocalizedMessage(), MYPOIException.MYPOIEXCEPIONTYPE_PRINTLN);
			} catch (IOException e){
				throw new MYPOIException("", MYPOIException.MYPOIEXCEPIONTYPE_PRINTLN);
			}
		}
		
	}
	/**
	 * 获取配置文件中的配置参数
	 * @return
	 */
	private static PropertiesVO initConfigurations(boolean isUseDefault) {
		PropertiesVO propertiesVO = new PropertiesVO();
		if(isUseDefault){
			return propertiesVO.initDefault();
		}
		try {

			InputStream inputStream = propertiesVO.getClass().getClassLoader().getResourceAsStream("config/init.properties");
			Properties p = new Properties();
			p.load(inputStream);
			propertiesVO.initDefault();
			Method fieldSetMet;
			String fieldSetName;
			for (Field field : propertiesVO.getClass().getDeclaredFields()) {
				if (p.getProperty(field.getName().toUpperCase()) != null) {
					fieldSetName = parSetName(field.getName());
					if (checkSetMet(propertiesVO.getClass().getMethods(),
							fieldSetName)) {
						fieldSetMet = propertiesVO.getClass().getMethod(
								fieldSetName, field.getType());
						fieldSetMet.invoke(propertiesVO,
								p.getProperty(field.getName().toUpperCase()));

					}
				}
			}
		} catch (Exception e) {
			propertiesVO.initDefault();
		}

		return propertiesVO;
	}
	
	
    /**  
     * 判断是否存在某属性的 set方法  
     * @param methods  
     * @param fieldSetMet  
     * @return boolean  
     */  
    public static boolean checkSetMet(Method[] methods, String fieldSetMet) {   
        for (Method met : methods) {   
            if (fieldSetMet.equals(met.getName())) {   
                return true;   
            }   
        }   
        return false;   
    } 
    /**  
     * 拼接在某属性的 set方法  
     * @param fieldName  
     * @return String  
     */  
    public static String parSetName(String fieldName) {   
        if (null == fieldName || "".equals(fieldName)) {   
            return null;   
        }   
        return "set" + fieldName.substring(0, 1).toUpperCase()   
                + fieldName.substring(1);   
    }   

}
